So now that we've created our data model based off of bulk customer info and order info and we created
the relationship between those two data sets we can now report off of that data using a pivot table
.
So let's take a look at how we can create a pivot table based off of our newly modeled data model.
All right so I'm back inside the exercise file.
Same one I've been using.
I'm going to go to the power pivot tab and I'm going to open up my data model.
I'm going to go to the Manage button.
This just really opens up the same window that we experienced in the previous lecture when we were adding
our data sets to the data model and creating the relationships.
So I've got my two tabs down there.
Customer hand-phone order info.
And now I want to create the pivot table cell up here in my hometown top of my stream within power pivots
.
About halfway down.
I've got my pivot table tool so I'll give that a click and I'm going to create a new pivot table and
I saw Excel kind of takes over from here.
I'm moving away from the data model window or the power pivot window.
And now I'm back inside of the Excel application looks a little bit different than what we experienced
creating the standard pivot table.
But first step I mean to tell it hey I'm going to put this into a new worksheet.
I'll hit OK I've got my new sheet down below.
I'll go ahead and give that a double quick just to rename this and it's double click that sheet 7 or
whatever number you got there and I'll call this model pivot table just so I recognize that this is
the one we're working with.
So now looking over into our pivot table field list I can see that I've got my two tables in there.
I got the customer info table with all the fields from that customer info section and I've got my order
info table in there as well with all the fields from that table and they're related to one another.
Let's just try creating a simple pivot table based off of these multiple sets of data through our data
model.
So I'd say from the customer info table I'd like to get their country and is going to left click drag
that to the Rose and I've now got all the countries for all my various customers.
All right.
Now that's coming from the customer info table.
Let's close that up from the Order table.
I want to know.
I want to do something about their freight's Let's get an average freight for these different countries
.
So I grab freight drag that down to the value section.
What do we get by default this is a numeric value to an average gets it summed it well you that some
are afraid that's great now I've got total freight by country two different datasets one for customers
one from orders.
But because of the data model we can combine them create the relationships and then report off of it
.
Now you know what I don't want some freight's What's changed that so I'll give that a left click lets
do value field settings and I'll change that to an average.
Let's find out our average cost for freight for these different countries for our different customers
.
So it's as simple as that.
But a very powerful tool that we can grab data sets or lists whether they're from Excel or from some
other source some other database online services.
We can create relationships between them and now we can create our pivot tables.
So try this out get your data model going get your two sets of data in there create the relationships
and then launch the pivot table tool from your power pivot window.
Try it out